import os
import pandas as pd
import mysql.connector

basic_path = str(os.path.dirname(__file__)) + "\\"
grade_sim_path = basic_path + "..\\simer\\sim\\school_grade_sim.csv"

def init_grade_sim(recreate=False):
    df = pd.read_csv(grade_sim_path, encoding="utf-8-sig")
    con = mysql.connector.connect(
        host="192.168.80.190",
        port=3306,
        user="hive",
        password="12345678",
        database="gaokao"
    )
    cursor = con.cursor()
    columns = ["province_name", "school_name", "year", "batch_name", "type_name", "require_json", "require_str",
               "min_grade", "min_rank", "num_student"]
    if recreate:
        drop_query = "DROP TABLE IF EXISTS grade_sim"
        cursor.execute(drop_query)
        create_query = "CREATE TABLE grade_sim " \
                       "(province_name VARCHAR(20) NOT NULL," \
                       " school_name VARCHAR(50) NOT NULL ," \
                       " year VARCHAR(10) NOT NULL ," \
                       " batch_name VARCHAR(100)," \
                       " type_name VARCHAR(100)," \
                       " require_json VARCHAR(100)," \
                       " require_str VARCHAR(50)," \
                       " min_grade INT, min_rank INT default 0, num_student INT default 0)"
        cursor.execute(create_query)
        index_query = "create index province_index on grade_sim(province_name)"
        cursor.execute(index_query)
        index_query = "create index school_index on grade_sim(school_name)"
        cursor.execute(index_query)
        index_query = "create index year_index on grade_sim(year)"
        cursor.execute(index_query)
        con.commit()
    insert_query = "INSERT INTO grade_sim (province_name, school_name, year, batch_name, type_name, require_json, require_str, min_grade, min_rank, num_student) " \
                      "VALUES ('%s', '%s', '%s', '%s', '%s', '%s', '%s', %d, %d, %d)"
    for i in df.iterrows():
        row = i[1]
        q = insert_query % (row["province_name"], row["school_name"], row["year"], row["batch_name"], row["type_name"], row["require_json"], row["require_str"], row["min_grade"], row["min_rank"], row["num_student"])
        q = q.replace("nan", "")
        cursor.execute(q)
        con.commit()
    cursor.close()
    con.close()

init_grade_sim(True)